Creating Measures
9
Create the following DAX Measures
1. 1. .Total Hire.refactor Interested =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14)
&& JobSubmissions[SourceID] = 3))
2. 2. .Total Hire.refactored.ai Submission _new =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 3
)
)
3. 3. .Total Huntr Interested =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14)
&& JobSubmissions[SourceID] = 4))
4. 4. .Total Huntr Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 4
)
)
5. 5. .Total Huntr Submission =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 4
)
)
6. 6. .Total Huntr Submission_new =
CALCULATE(COUNT(JobSubmissions[JobID]),
FILTER (
JobSubmissions, JobSubmissions[SourceID] = 4
)
)
7. .Arrow Indicator Interest =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
RETURN
IF ([Total Interest] < 0, Down,
IF([Total Interest] > 0, Up, " " )
)
8..Arrow Indicator InterTOPlcm =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
RETURN
IF ( [Rolling Month INtvtoPlcmnt] < 0, Down,
IF([Rolling Month INtvtoPlcmnt] > 0, Up, " " )
)
9. .Arrow Indicator InterToPlcmWeek =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ([Rolling Week IntrvtoPlcmnt] < 0, Down,
IF([Rolling Week IntrvtoPlcmnt] > 0, Up, " " )
)
10. .Arrow Indicator IntrTosubMonth =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ( [Rolling Month InttoSubm] < 0, Down,
IF([Rolling Month InttoSubm] > 0, Up, " " )
)
11. .Arrow Indicator IntrTosubWeek =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ( [Rolling Week InttoSubm] < 0, Down,
IF([Rolling Month InttoSubm] > 0, Up, " " )
)
12. .Arrow Indicator SubToIntrMonth =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
RETURN
IF ( [Rolling Month SubmtoIntrv] < 0, Down,
IF([Rolling Month SubmtoIntrv] > 0, Up, " " )
)
13. .Arrow Indicator SubToIntWeek =
VAR Up =
UNICHAR ( 9650 )
VAR Down =
UNICHAR ( 9660 )
VAR Color = IF(Up, "Green", "Red")
RETURN
IF ([Rolling Week Sub to Intrview] < 0, Down,
IF([Rolling Week Sub to Intrview] > 0, Up, " " )
)
14. .Gauging Interest =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14
|| JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 16
)
)
15. cc.Intiating Contact =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 4
)
)
16. .Last Month Interview to Placement Ratio =
CALCULATE (
DIVIDE ( [Total PLacement], [Total Interview] ),
( DATESINPERIOD ( 'Date'[Date], TODAY (), -1, MONTH ) )
)
17. .LM Submission to Interview Ratio =
CALCULATE (
DIVIDE ( [Total Interview], [Total Submission] ),
( DATESINPERIOD ( 'Date'[Date], TODAY (), -1, MONTH ) )
)
18. .Not Promising =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 9
|| JobSubmissions[JS_ID] = 15
|| JobSubmissions[JS_ID] = 10
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 11
)
)
19. .Promising =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 5
|| JobSubmissions[JS_ID] = 8
|| JobSubmissions[JS_ID] = 7
)
)
20. .Total Hire.refactored.ai Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 3
)
)
21. .Total Hire.refactored.ai Submission _new =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 3
)
)
22. .Total Huntr Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 4
)
)
.Total Huntr Submission =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 4
)
)
23. .Total Huntr Submission_new =
CALCULATE(COUNT(JobSubmissions[JobID]),
FILTER (
JobSubmissions, JobSubmissions[SourceID] = 4
)
)
24. .Total Sales Rep Interested =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14 )
&& JobSubmissions[SourceID] = 2
)
)
25. .Total Sales Rep Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 2
)
)
26.
.Total Sales Rep Interview = CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 2
)
)
27. .Total Sales Rep Submission_new =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 2
)
)
28. .Total Student Interview =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4)
&& JobSubmissions[SourceID] = 1
)
)
29. .Total Student Sub Interested =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
(JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14)
&& JobSubmissions[SourceID] = 1
)
)
30. .Total Student Submission_new =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
( JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
&& JobSubmissions[SourceID] = 1
)
)
31. .TTM_Hire.refactor_interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Hire.refactor Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
32. .TTM_Hire.refactor_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Hire.refactored.ai Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
33. .TTM_Hire.refactor_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Hire.refactored.ai Submission _new],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
34. .TTM_Huntr_Interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
35. .TTM_Huntr_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
36..TTM_Huntr_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Submission],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
37. .TTM_Huntr_Jobsubmission new =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Huntr Submission],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
38. .TTM_Interest to submission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total Submission], [Total Interest], 0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result + 0
39. .TTM_Interview to Placement =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total PLacement], [Total Interview], 0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
40. .TTM_Sales Rep_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Sales Rep Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
41. .TTM_Sales Reps' interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Sales Rep Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
42. .TTM_Sales Reps'_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Sales Rep Submission_new],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
43. .TTM_Student Sub_interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Student Sub Interested],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
44. .TTM_Student_JobInterview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Student Interview],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
45. .TTM_Student_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
[.Total Student Submission_new],
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
46. .TTM_Submission_Interview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total Interview],[Total Submission], 0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
47. CURRENTWEEK =
SELECTEDVALUE ( 'Date'[Week Number] )
48. Interest to Submission =
VAR _TOTALSUBMITTED =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER ( JobSubmissions,
JobSubmissions[JS_ID] IN {1, 6, 12, 15}
)
)
VAR _TOTALINTERESTED =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14
)
)
RETURN
DIVIDE ( _TOTALSUBMITTED, _TOTALINTERESTED, 0 ) + 0
49. Submission to Interview Ratio = DIVIDE([Total Interview],[Total Submission],0) + 0
50. TTM_ Interest =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[ID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 13
|| JobSubmissions[JS_ID] = 14
)
)
RETURN
Result
51. TTM_ Interview =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[ID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 2
|| JobSubmissions[JS_ID] = 3
|| JobSubmissions[JS_ID] = 4
)
)
RETURN
Result
52.
TTM_ Placement = VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[ID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 7
|| JobSubmissions[JS_ID] = 8
)
)
RETURN
Result
53. TTM_Interview to Placement =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
DIVIDE([Total PLacement],[Total Interview],0),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
54. TTM_Jobsubmission =
VAR CurrentDate = MAX('Date1'[WeekSEnd])
VAR PreviousDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate)-3 , WEEKNUM(CurrentDate) -12)
VAR Result =
CALCULATE(
CALCULATE(DISTINCTCOUNT(JobSubmissions[ID]),FILTER(JobSubmissions,JobSubmissions[JS_ID]=1)),
FILTER(
'JobSubmissions',
'JobSubmissions'[WeekSEnd] >= PreviousDate && 'JobSubmissions'[WeekSEnd] <= CurrentDate
)
)
RETURN
Result
55. Total Jobs = ROUND(DISTINCTCOUNT(JobSubmissions[JobID]),0)
56. Total PLacement =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER (
JobSubmissions,
JobSubmissions[JS_ID] = 7
|| JobSubmissions[JS_ID] = 8
)
)
57. Total Submission =
CALCULATE (
DISTINCTCOUNT ( JobSubmissions[JobID] ),
FILTER ( JobSubmissions, JobSubmissions[JS_ID] = 1
|| JobSubmissions[JS_ID] = 6
|| JobSubmissions[JS_ID] = 12
|| JobSubmissions[JS_ID] = 15)
)
58. Interview to Placement Ratio New = DIVIDE([Total PLacement],[Total Interview],7) + 0
59. Interview to Placement Ratio = DIVIDE([Total PLacement],[Total Interview],0) + 0
60. Interest to submission1 = DIVIDE([Total Interest],[Total Submission],0) + 0